﻿using System;
using System.Collections.Generic;
using System.Collections.Specialized;
using System.Linq;
using System.Text;
using System.Text.RegularExpressions;
using System.Threading.Tasks;

public class ReqParam
{
    /// <summary>
    /// 当前页
    /// </summary>
    public int CurrentPage { get; set; }
    /// <summary>
    /// 页大小
    /// </summary>
    public int PageSize { get; set; }
    /// <summary>
    /// 排序
    /// </summary>
    public string Orders { get; set; }

    /// <summary>
    /// 提交参数
    /// </summary>
    public NameValueCollection Params { get; set; }

    /// <summary>
    /// 列名 对应 表名/表别名
    /// </summary>
    public NameValueCollection NameMapping { get; set; }

    /// <summary>
    /// Where 条件
    /// </summary>
    public List<string> LstWhere { get; set; }

    /// <summary>
    /// Where 条件
    /// </summary>
    public string Where { get; set; }

    /// <summary>
    /// 是否检查提交字段，防止SQL注入
    /// </summary>
    public bool IsCheckSql { get; private set; }

    /// <summary>
    /// ReqParam 构造1
    /// </summary>
    /// <param name="nvc">提交参数</param>
    /// <param name="isCheckSql">是否检查提交字段，防止SQL注入</param>
    public ReqParam(NameValueCollection nvc, bool isCheckSql = false)
        : this(nvc, null, isCheckSql)
    {

    }

    /// <summary>
    /// ReqParam 构造2
    /// </summary>
    /// <param name="nvc">提交参数</param>
    /// <param name="nameMapping">列名 对应 表名/表别名</param>
    /// <param name="isCheckSql">是否检查提交字段，防止SQL注入</param>
    public ReqParam(NameValueCollection nvc, NameValueCollection nameMapping, bool isCheckSql = false)
    {
        this.IsCheckSql = isCheckSql;
        this.NameMapping = nameMapping;

        if (nvc != null && nvc.Count > 0)
        {
            nvc = new NameValueCollection(nvc);

            this.CurrentPage = (nvc["pageCurrent"] == null) ? 1 : Convert.ToInt32(nvc["pageCurrent"]);
            this.PageSize = (nvc["pageSize"] == null) ? 20 : Convert.ToInt32(nvc["pageSize"]);
            this.Orders = nvc["orders"];

            nvc.Remove("pageCurrent");
            nvc.Remove("pageSize");
            nvc.Remove("orders");

            //导出 下载传递的数据
            nvc.Remove("mappingNames");
            nvc.Remove("outPutFileName");
            nvc.Remove("ajaxRequest");

            this.Params = nvc;
        }
        else
        {
            this.Params = new NameValueCollection();
        }

        if (Params.Count > 0)
        {
            this.LstWhere = new List<string>();

            string[] oneKeys = Params.AllKeys.Where(t => t.EndsWith("operator")).ToArray();

            for (int j = 0; j < oneKeys.Length; j++)
            {
                string key = oneKeys[j];
                string name = key.Replace(".operator", "");
                string strOperator = Params.Get(key);
                string value = Params.Get(name);

                if (NameMapping != null)
                {
                    name = NameMapping.Get(name) ?? name;
                }

                string where = FormatWhere(name, strOperator, value);

                LstWhere.Add(where);
            }

            string[] twoKeys = Params.AllKeys.Where(t => t.EndsWith(".operator[]")).ToArray();
            for (int j = 0; j < twoKeys.Length; j++)
            {
                string key = twoKeys[j];
                string name = key.Replace(".operator[]", "");
                string[] opValus = Params.GetValues(key);
                string[] values = Params.GetValues(name + "[]");
                string andor = Params.Get(name + ".andor");

                if (NameMapping != null)
                {
                    name = NameMapping.Get(name) ?? name;
                }

                string where = "(" + FormatWhere(name, opValus[0], values[0]) + " " + andor + " " + FormatWhere(name, opValus[1], values[1]) + ")";

                LstWhere.Add(where);
            }

            if (oneKeys.Length == twoKeys.Length && twoKeys.Length == 0)
            {
                foreach (string key in Params.AllKeys)
                {
                    string where = key + "='" + Params[key] + "'";
                    LstWhere.Add(where);
                }
            }
            this.Where = string.Join(" and ", LstWhere);
        }
    }



    /// <summary>
    /// 格式化 列名 与 值
    /// </summary>
    /// <param name="name"></param>
    /// <param name="strOp"></param>
    /// <param name="value"></param>
    /// <returns></returns>
    private string FormatWhere(string name, string strOp, string value)
    {
        if (IsCheckSql)
        {
            value = CheckSql(value);
        }
        value = value.Replace("'", "");
        string where = string.Empty;
        switch (strOp)
        {
            case "like":
                where = name + " " + strOp + " '%" + value + "%'";
                break;
            default:
                where = name + " " + strOp + " '" + value + "'";
                break;
        }
        return where;
    }

    /// <summary>
    /// 验证Sql 防止Sql注入
    /// </summary>
    /// <param name="value"></param>
    /// <returns></returns>
    public static string CheckSql(string value)
    {
        if (string.IsNullOrEmpty(value))
        {
            return string.Empty;
        }
        else
        {
            value = Regex.Replace(value, "xp_cmdshell", "", RegexOptions.IgnoreCase);
            //删除与数据库相关的词
            value = Regex.Replace(value, "select", "", RegexOptions.IgnoreCase);
            value = Regex.Replace(value, "insert", "", RegexOptions.IgnoreCase);
            value = Regex.Replace(value, "delete from", "", RegexOptions.IgnoreCase);
            value = Regex.Replace(value, "count''", "", RegexOptions.IgnoreCase);
            value = Regex.Replace(value, "drop table", "", RegexOptions.IgnoreCase);
            value = Regex.Replace(value, "truncate", "", RegexOptions.IgnoreCase);
            value = Regex.Replace(value, "asc", "", RegexOptions.IgnoreCase);
            value = Regex.Replace(value, "mid", "", RegexOptions.IgnoreCase);
            value = Regex.Replace(value, "char", "", RegexOptions.IgnoreCase);
            value = Regex.Replace(value, "xp_cmdshell", "", RegexOptions.IgnoreCase);
            value = Regex.Replace(value, "exec master", "", RegexOptions.IgnoreCase);
            value = Regex.Replace(value, "net localgroup administrators", "", RegexOptions.IgnoreCase);
            value = Regex.Replace(value, "and", "", RegexOptions.IgnoreCase);
            value = Regex.Replace(value, "union", "", RegexOptions.IgnoreCase);

            return value;
        }
    }
}